/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dados.SQLConnection;

import dados.Cripto;
import dados.MedicamentoNormal;
import dados.Medico;
import dados.MedicoJaExisteException;
import dados.MedicoNaoExisteException;
import dados.Receita;
import dados.ReceitaEspecial;
import dados.ReceitaNormal;
import dados.ReceitaRenovavel;
import dados.Utente;
import dados.UtenteJaExisteException;
import dados.UtenteNaoExisteException;
import java.sql.*;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.StringTokenizer;


/**
 *
 * @author rmb
 */
public class SQLConnect {
    
    private Connection connection;
    
    public SQLConnect() {
        connection = null;
    }
    
    public Connection getConnection() {
        return connection;
    }
    
    public void connect() {        
            try {                
                String driverName = "oracle.jdbc.driver.OracleDriver";
                Class.forName(driverName);
                
                String serverName = "gia-oracle.di.uminho.pt";
                String portNumber = "1521";
                String sid = "osiris";
                String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
                String username = "a54770";
                String password = "a54770";
                connection = DriverManager.getConnection(url, username, password);
                Statement st = connection.createStatement();
                st.execute("alter session set NLS_COMP=ANSI; alter session set NLS_SORT=BINARY_CI;");
          } catch (ClassNotFoundException e) {} 
            catch (SQLException e) {}     
    }
    
    
    public void connectLocalHost() {        
            try {                
                String driverName = "oracle.jdbc.driver.OracleDriver";
                Class.forName(driverName);

                String serverName = "172.16.5.130";
                String portNumber = "1521";
                String sid = "xe";
                String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
                String username = "rmb";
                String password = "rmb";
                connection = DriverManager.getConnection(url, username, password);
          } catch (ClassNotFoundException e) {} 
            catch (SQLException e) {}     
    }
    
    public void commit() {
        if(connection != null) {
            try {
                connection.commit();
            } catch (SQLException e) {}
        }
    }
   
    public void closeConnection () {
        if (connection != null) {
            try{
                connection.close();
            }
            catch(SQLException e) {}
        
        }        
    }
                           
    
    
    public String getLocalidadeDescr (String cod_postal) {
        
        String descr = null;
        String quer = "select descricao from cod_postal where cod_postal = ?";
        
        try {
            PreparedStatement ps = connection.prepareStatement(quer);
            ps.setString(1, cod_postal);
            ResultSet rs = ps.executeQuery();
                                    
            while(rs.next())
                descr = rs.getString("descricao");
            
        }
        catch(SQLException e) {}
        
        return descr;        
    }
    
    
    
    

    
        
}
